﻿Imports System.IO

Public Class ClassEmployee
    Public Shared Function addEmployee(ByVal name As String, ByVal sex As String, ByVal phone As String, ByVal address As String, ByVal dob As Date, ByVal pob As String, ByVal join As Date, ByVal photo As Image)
        Dim sql = <sql>
                      insert into tbl_employee(employee_name,sex,phone,address,dob,pob,join_date,active,photo) 
                      value(@name,@sex,@phone,@address,@dob,@pob,@join,1,@photo)
                  </sql>
        Try
            Cmd.Parameters("@name") = name
            Cmd.Parameters("@sex") = sex
            Cmd.Parameters("@phone") = phone
            Cmd.Parameters("@address") = address
            Cmd.Parameters("@dob") = dob
            Cmd.Parameters("@pob") = pob
            Cmd.Parameters("@join") = join

            Dim MemStream As New MemoryStream
            Dim DataPic_Update As Byte()
            photo.Save(MemStream, Imaging.ImageFormat.Jpeg)
            DataPic_Update = MemStream.GetBuffer()
            MemStream.Read(DataPic_Update, 0, MemStream.Length)
            Cmd.Parameters("@photo") = DataPic_Update

            Cmd.ExecuteNonQuery(sql)
            myMsgbox.show("Add new employee success!!!", "New Employee")
            Return True
        Catch ex As Exception
            myMsgbox.show("Add new employee fail!!!", "New Employee")
            Return False
        End Try
    End Function

    Public Shared Function EditEmployee(ByVal id As Integer, ByVal name As String, ByVal sex As String, ByVal phone As String, ByVal address As String, ByVal dob As Date, ByVal pob As String, ByVal join As Date, ByVal photo As Image)
        Dim sql = <sql>
                    update tbl_employee set employee_name=@name,sex=@sex,address=@address,dob=@dob,pob=@pob,join_date=@join,photo=@photo
                </sql>
        Try
            Cmd.Parameters("@id") = id
            Cmd.Parameters("@name") = name
            Cmd.Parameters("@sex") = sex
            Cmd.Parameters("@phone") = phone
            Cmd.Parameters("@address") = address
            Cmd.Parameters("@dob") = dob
            Cmd.Parameters("@pob") = pob
            Cmd.Parameters("@join") = join

            Dim MemStream As New MemoryStream
            Dim DataPic_Update As Byte()
            photo.Save(MemStream, Imaging.ImageFormat.Jpeg)
            DataPic_Update = MemStream.GetBuffer()
            MemStream.Read(DataPic_Update, 0, MemStream.Length)
            Cmd.Parameters("@photo") = DataPic_Update

            Cmd.ExecuteNonQuery(sql)
            myMsgbox.show("Edit Employee Success!!!", "Edit Employee")
            Return True
        Catch ex As Exception
            myMsgbox.show("Edit Employee Fail!!!", "Edit Employee")
            Return False
        End Try
    End Function

    Public Shared Function deleteEmployee(ByVal id As Integer)
        Dim sql = "update tbl_employee set active=false where employee_id=@id"
        Try
            Cmd.Parameters("@id") = id
            Cmd.ExecuteNonQuery(sql)
            myMsgbox.show("Delete Employee Success!!!", "Delete Employee")
            Return True
        Catch ex As Exception
            myMsgbox.show("Delete Employee Fail!!!", "Delete Employee")
            Return False
        End Try
    End Function

    Public Shared Function loadEmployee(ByVal text As String) As DataTable
        Dim sql = <sql>
                    select * from tbl_employee where UPPER(employee_name) like UPPER(@name) and active=true
                </sql>
        Try
            Cmd.Parameters("@name") = "%" & text & "%"
            Dim dt As New DataTable
            dt = Cmd.ExecuteDataTable(sql)
            Return dt
        Catch ex As Exception
            Return Nothing
        End Try
    End Function
End Class
